
USE CairoSitters

GO

---------------Registrations----------------------

CREATE PROCEDURE AccountIDAssignment
@acctID INTEGER
AS 
INSERT INTO Accounts(AccountTypeID)
VALUES (@acctID)
SELECT SCOPE_IDENTITY()

GO



CREATE PROCEDURE ParentRegistration1

@MEMail			varchar(50),
@MotherName		varchar(50),
@MDoB			date,
@MCellNo		varchar(50),
@MPhoneNo		varchar(50),
@MEmployment	varchar(50),
@MStreetAddress	varchar(50),
@MArea			varchar(50),
@MPostalCode	varchar(50),
@MCityID		INTEGER,

@FEMail			varchar(50),
@FatherName		varchar(50),
@FDoB			date,
@FCellNo		varchar(50),
@FPhoneNo		varchar(50),
@FEmployment	varchar(50),
@FStreetAddress	varchar(50),
@FArea			varchar(50),
@FPostalCode	varchar(50),
@FCityID		INTEGER,

@accID Integer

AS 

INSERT INTO Parents(AccountID, ParentName, EMail, DoB, Gender, CellNo, PhoneNo, Employment, StreetAddress, Area, PostalCode, CityID)
VALUES(@accID, @MotherName, @MEMail, @MDoB, 'F', @MCellNo, @MPhoneNo, @MEmployment, @MStreetAddress, @MArea, @MPostalCode, @MCityID)

INSERT INTO Parents(AccountID, ParentName, EMail, DoB, Gender, CellNo, PhoneNo, Employment, StreetAddress, Area, PostalCode, CityID)
VALUES(@accID, @FatherName, @FEMail, @FDoB, 'M', @FCellNo, @FPhoneNo, @FEmployment, @FStreetAddress, @FArea, @FPostalCode, @FCityID)

GO


CREATE PROCEDURE ParentRegistration2Child
@acctID INTEGER,
@child_name varchar(50),
@DoB date,
@gender char(1),
@schools varchar(50),
@grade varchar(50),
@health_conditions varchar(50)

AS

INSERT INTO Children(CName, AccountID, DoB, Gender, HealthConditions, Schools, Grade)
VALUES (@child_name, @acctID, @DoB, @gender, @health_conditions, @schools, @grade)

SELECT SCOPE_IDENTITY()


GO


CREATE PROCEDURE ParentRegistration2ChildLanguages
@childID INTEGER,
@languageID INTEGER

AS

INSERT INTO Children_Languages(ChildID, LanguageID)
VALUES (@childID, @languageID)

GO


CREATE PROCEDURE Registration_EmergencyContacts
@ec_name varchar(50),
@ec_mobile_num varchar(50),
@ec_phone_num varchar(50)
AS
INSERT INTO EmergencyContacts (ECName, MobileNo, PhoneNo)
VALUES (@ec_name, @ec_mobile_num, @ec_phone_num)

SELECT SCOPE_IDENTITY()

GO


CREATE PROCEDURE Registration_EmergencyContacts_A
@acctID INTEGER,
@ecID INTEGER,
@relationship varchar(50)
AS
INSERT INTO EmergencyContacts_Accounts (AccountID, ECID, Relationship)
VALUES (@acctID, @ecID, @relationship)


GO

CREATE PROCEDURE Registration_Auth
@acctID INTEGER,
@user_name varchar(50),
@pass_word varchar(50)
AS
UPDATE Accounts
SET UserName = @user_name, Pass_word = @pass_word
WHERE AccountID = @acctID

GO


CREATE PROCEDURE BabySittersRegistration1_Info
@acctID INTEGER,
@full_name varchar(50),
@DoB datetime,
@nationality_id INTEGER,
@gender char(1),
@cellNo	varchar(50),
@phoneNo varchar(50),
@email	varchar(50),
@address varchar(50),
@area varchar(50),
@cityID INTEGER,
@postalCode	varchar(50)

AS

INSERT INTO BabySitters(BSName, AccountID, DoB, NationalityID, Gender, CellNo, PhoneNo, EMail, BS_Address, Area, CityID, PostalCode)
VALUES (@full_name, @acctID, @DoB, @nationality_id, @gender, @cellNo, @phoneNo, @email, @address, @area, @cityID, @postalCode)

SELECT SCOPE_IDENTITY ()


GO  

CREATE PROCEDURE BabySittersRegistration2_Languages
@bsID INTEGER,
@languageID INTEGER

AS

INSERT INTO BabySitters_Languages(BSID, LanguageID)
VALUES (@bsID, @languageID)

GO

CREATE PROCEDURE SchoolUniversity
@name varchar(50)
AS
INSERT INTO SchoolsUniversities(SUName)
VALUES(@name)
SELECT SCOPE_IDENTITY()

GO 


CREATE PROCEDURE BabySittersRegistration1_Edu
@bsID INTEGER,
@suID INTEGER,
@years_completed INTEGER,
@grad_year INTEGER

AS 

INSERT INTO BSEducationalBackground(BSID, SUID, YearsCompleted, GraduationYear)
VALUES (@bsID, @suID, @years_completed, @grad_year)

GO


CREATE PROCEDURE BSEmployers
@e_name varchar(50)
AS
INSERT INTO Employers(EName)
VALUES(@e_name)
SELECT SCOPE_IDENTITY()

GO 


CREATE PROCEDURE BabySittersRegistration1_Emp
@bsID INTEGER,
@empID INTEGER,
@date varchar(50),
@duties varchar(max)

AS 

INSERT INTO BSEmploymentBackground(BSID, EDate, Duties, EmployerID)
VALUES (@bsID, @date, @duties, @empID)

GO


CREATE PROCEDURE BSHobbiesSkills
@h_name varchar(50)
AS
INSERT INTO HobbiesSkills(HSName)
VALUES(@h_name)
SELECT SCOPE_IDENTITY()

GO 


CREATE PROCEDURE BabySittersRegistration1_Hob
@bsID INTEGER,
@hsID INTEGER
AS 

INSERT INTO HSs_BSs(BSID, HSID)
VALUES (@bsID, @hsID)

GO


CREATE PROCEDURE BSExperience
@bsID INTEGER,
@experience varchar(50),
@essay varchar(MAX)
AS
UPDATE BabySitters
SET Experience = @experience, Essay = @essay
WHERE BSID = @bsID

GO


CREATE PROCEDURE BSHealth
@bsID INTEGER,
@allg varchar(max),
@pet varchar(MAX)
AS
UPDATE BabySitters
SET Allergies = @allg, Petconcern = @pet
WHERE BSID = @bsID

GO

CREATE PROCEDURE BabySittersRegistration3_con
@bsID INTEGER,
@clubs bit,
@cinemas bit,
@otherEvents bit,
@withparentsathome bit,
@withparentsatclub bit,
@withmaidathome bit,
@withmaidatclub bit,
@aloneathome bit,
@aloneatclub bit
AS

INSERT INTO BSWorkConditions(BSID, clubs, cinemas, otherEvents, withparentsathome, withparentsatclub, withmaidathome, withmaidatclub, aloneathome, aloneatclub)
VALUES (@bsID, @clubs, @cinemas, @otherEvents, @withparentsathome, @withparentsatclub, @withmaidathome, @withmaidatclub, @aloneathome, @aloneatclub)

GO


CREATE PROCEDURE BabySittersRegistration4_con
@bsID INTEGER,
@daycaretwosix bit,
@daycaresixtwelve bit,
@nightcare bit,
@workoffsched bit,
@hrsweek int,
@hrsday int

AS
UPDATE BSWorkConditions
SET daycaretwosix = @daycaretwosix, daycaresixtwelve = @daycaresixtwelve, nightcare = @nightcare, 
workoffsched = @workoffsched, hoursaweek = @hrsweek, hoursaday = @hrsday
WHERE BSID = @bsID


GO


CREATE PROCEDURE ScheduleDay
@bsID INTEGER,
@day varchar(50)
AS 
INSERT INTO BSSchedule(BSID, S_Day)
VALUES (@bsID, @day)

SELECT SCOPE_IDENTITY()

GO

CREATE PROCEDURE ScheduleHour
@bssID INTEGER,
@dhID INTEGER
AS
INSERT INTO DH_BSS(BSSID, DHID)
VALUES(@bssID, @dhID)



